Empresas
Empregos
  • Sobre nós
  • Soluções
    • Publicação de vagas
      Publique sua vaga e receba candidatos qualificados em 48h.
    • Avaliações de candidatos
      Mais de 500 testes técnicos e psicológicos, mais anti-fraude.
    • Headhunting
      Busca executiva personalizada do início ao fim.
    • Folha de Pagamento + EOR
      Dispersão de folha e EOR em mais de 15 países da LATAM.
  • Preços
  • Empregos

0

452
Visualizações
mysql> SELECT COUNT(*) vs SHOW TABLE STATUS for row count

We have a table in our database that has teens of millions of entries (10.1.21-MariaDB; InnoDB table engine; Windows OS). We are able to get the number of rows in the table instantaneously using the command SHOW TABLE STATUS LIKE 'my_table_name'. However, SELECT COUNT(*) FROM my_table_name takes a few minutes to complete.


Q) Why is SHOW TABLE STATUS LIKE 'my_table_name' so so much quicker than SELECT COUNT(*) FROM my_table_name?

over 4 years ago · Santiago Trujillo
2 Respostas
Responde à pergunta

0

Because one is a query that counts all the rows and the other is a command that retrieves stats the DB engine maintains about the table. There isn't any firm guarantee that the table statistic will be up to date so the only way to get an accurate count is to count the rows, but it might be that you don't need it to be perfectly accurate all the time. You can thus choose either, depending on your desire for accuracy vs speed etc.

See here this screenshot from https://pingcap.com/docs/stable/sql-statements/sql-statement-show-table-status/

enter image description here

You can see the example inserts 5 rows but the table stats are out of date and the table still reports 0 rows. Running ANALYZE TABLE will (probably) take longer than counting the rows, but the stats will be up to date (for a while at least) afterwards.

over 4 years ago · Santiago Trujillo Relatório

0

A suggested approach to get a reasonably accurate count of the table size, when SELECT COUNT(*) is taking a long time to complete, could be:
ANALYZE TABLE my_table_name; SHOW TABLE STATUS LIKE 'my_table_name';

This comes in especially handy when importing a large amount of data into a table, and you want to track the progress of the import process.

over 4 years ago · Santiago Trujillo Relatório
Responde à pergunta
Encontrar trabalhos remotos

Descubra a nova forma de encontrar um emprego!

melhores empregos
Principais categorias de trabalho
Empresas
Postar vaga Preços Comercial
Jurídico
Termos e Condições Política de privacidade
© 2026 PeakU Inc. All Rights Reserved.
Andres GPT
Recomende algumas ofertas para mim
Preciso de ajuda